ALTER  VIEW [dbo].[vw_Sort_Wip_View] AS(
SELECT  
	LT.PROCESSSTEP_ID, (SELECT L_VENDOR_NAME FROM LOCATION WHERE LOCATION_ID = LT.LOCATION_ID) AS LOCATION, 
	LOT_NUMBER,FAB_LOT_NUMBER, ISNULL(LOT_CATEGORY,'PROD') AS LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, 
	STAGE_NAME, STAGE_POSITION,LOT_CURRENT_STATUS,SL_EXPECTED_DATE_OUT,
	SL_HOLD_DATE, SL_TESTER, SL_FLOW,SL_TEST_PROGRAM,
	( convert(int,CEILING(cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Gross Die/Wafer' 
					AND PROCESSSTEP_ID = (SELECT Top 1 PROCESSSTEP_ID FROM PROCESS_STEP_TRAVERSE_TABLE WHERE NEXT_PROCESSSTEP_ID = (SELECT Top 1 PROCESSSTEP_ID FROM PROCESS_STEP_TRAVERSE_TABLE WHERE NEXT_PROCESSSTEP_ID = P.PROCESSSTEP_ID )) ) as float) ) )
	)AS GROSS_DIE_WAFER,
	NULL AS WAFER_ID,PART_NUMBER,
	(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
		CASE 
			WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
		END 
	) AS BASE_PRODUCT,
	(CASE 
		WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
		WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
	END) AS BASE_PRODUCT_ID ,PS_TYPE_NAME, PS_TYPE, 5 AS PS_TYPE_ID, LT.LOT_ID, LT.LOCATION_ID
FROM 
	LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS, SORT_LOT SL
WHERE 
	LT.LOT_ID = SL.LOT_ID
	AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
	AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	AND WAFER_QTY_IN > 0
	AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )
	--AND P.OBSOLETE != 0
	AND PS_TYPE_NAME = 'Sort'
)
